package com.eyas.core.jdbc.utils;

import com.eyas.core.annotation.*;
import com.eyas.core.annotation.check.CheckAnnotationUtils;
import com.eyas.core.model.page.PageResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;

import javax.validation.constraints.NotNull;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @Auther: 王龙龙
 * @Date: 2019/2/21 14:01
 * @Description:
 */
public final class CommonJdbcUtils implements JdbcOperate {
    private final static Logger logger = LoggerFactory.getLogger(CommonJdbcUtils.class);
    private JdbcTemplate jdbcTemplate;
    public static CommonJdbcUtils commonJdbcUtils;
    private final Set<Class> JAVA_BASIC_TYPE;

    private enum SCRIPT_TYPE {DML, PROCEDURE}

    private enum DML_TYPE {INSERT, UPDATE, DELETE}

    public CommonJdbcUtils(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        CommonJdbcUtils.commonJdbcUtils = this;
        JAVA_BASIC_TYPE = new HashSet<>();
        JAVA_BASIC_TYPE.add(short.class);
        JAVA_BASIC_TYPE.add(int.class);
        JAVA_BASIC_TYPE.add(long.class);
        JAVA_BASIC_TYPE.add(float.class);
        JAVA_BASIC_TYPE.add(double.class);
        JAVA_BASIC_TYPE.add(boolean.class);
        JAVA_BASIC_TYPE.add(byte.class);
        JAVA_BASIC_TYPE.add(char.class);
        JAVA_BASIC_TYPE.add(Short.class);
        JAVA_BASIC_TYPE.add(Integer.class);
        JAVA_BASIC_TYPE.add(Long.class);
        JAVA_BASIC_TYPE.add(Float.class);
        JAVA_BASIC_TYPE.add(Double.class);
        JAVA_BASIC_TYPE.add(Boolean.class);
        JAVA_BASIC_TYPE.add(Byte.class);
        JAVA_BASIC_TYPE.add(String.class);
    }


    @Override
    public <T> int update(@NotNull T model) {
        if (model == null)
            return 0;
        long start = System.currentTimeMillis();
        this.checkModel(model, SCRIPT_TYPE.DML);
        long e1 = System.currentTimeMillis();
        ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.UPDATE);
        long e2 = System.currentTimeMillis();
        long start2 = System.currentTimeMillis();
        int var = this.jdbcTemplate.update(modelMapper.sql, modelMapper.args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + modelMapper.sql + "]--totalTime:" + (end - start) + " ms={checkModel:" + (e1 - start) + " ms,modelMapper:" + (e2 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return var;
    }

    @Override
    public <T> int[] batchUpdate(List<T> models) {
        if(models==null||models.size()==0)
            return null;
        long start = System.currentTimeMillis();
        List<Object[]> batchUpdateArgs = new ArrayList<>();
        String sql = "";
        int index = 0;
        for(T model:models) {
            index++;
            this.checkModel(model, SCRIPT_TYPE.DML);
            ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.UPDATE);
            batchUpdateArgs.add(modelMapper.args);
            if(index==1) {
                sql = modelMapper.sql;
            }
        }
        long e1 = System.currentTimeMillis();
        if(batchUpdateArgs.size()==0)
            return null;
        long start2 = System.currentTimeMillis();
        JdbcBatchPreparedStatementSetter jdbcBatchPreparedStatementSetter = new JdbcBatchPreparedStatementSetter(batchUpdateArgs);
        int[] result = this.jdbcTemplate.batchUpdate(sql,jdbcBatchPreparedStatementSetter);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]--totalTime:" + (end - start) + " ms={checkModel&modelMapper:" + (e1 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return result;
    }

    @Override
    public <T> int insert(@NotNull T model) {
        if (model == null)
            return 0;
        long start = System.currentTimeMillis();
        this.checkModel(model, SCRIPT_TYPE.DML);
        long e1 = System.currentTimeMillis();
        ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.INSERT);
        long e2 = System.currentTimeMillis();
        long start2 = System.currentTimeMillis();
        //int var = this.jdbcTemplate.update(modelMapper.sql, modelMapper.args);
        final int[] var = {0};
        //获取自增主键
        Object result = this.jdbcTemplate.execute(new PreparedStatementCreator() {
                                      @Override
                                      public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                                          PreparedStatement preparedStatement = con.prepareStatement(modelMapper.sql,modelMapper.primarykeynames);
                                          if(modelMapper.args!=null&&modelMapper.args.length>0) {
                                              for(int i=0;i<modelMapper.args.length;i++) {
                                                  StatementCreatorUtils.setParameterValue(preparedStatement, i+1, SqlTypeValue.TYPE_UNKNOWN, modelMapper.args[i]);
                                              }
                                          }
                                          return preparedStatement;
                                      }
                                  },
                new PreparedStatementCallback<Object>() {
                    public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                        var[0] = ps.executeUpdate();
                        ResultSet generatedKeys = ps.getGeneratedKeys();
                        while (generatedKeys.next()) {
                            return generatedKeys.getInt(1);
                        }
                        return null;

                    }
                });

        //获取到新增记录的主键后，将主键赋值到model对象中
        if(result!=null) {
            try {
                model.getClass().getMethod("set" + upperFirstChar(modelMapper.primaryfilednames[0]),modelMapper.primaryfiledtype).invoke(model,result);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        logger.info("sql:[" + modelMapper.sql + "]--totalTime:" + (end - start) + " ms={checkModel:" + (e1 - start) + " ms,modelMapper:" + (e2 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return var[0];//返回结果是insert的条数
    }

    @Override
    public <T> int[] batchInsert(List<T> models) {
        if(models==null||models.size()==0)
            return null;
        long start = System.currentTimeMillis();
        List<Object[]> batchUpdateArgs = new ArrayList<>();
        String sql = "";
        int index = 0;
        for(T model:models) {
            index++;
            this.checkModel(model, SCRIPT_TYPE.DML);
            ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.INSERT);
            batchUpdateArgs.add(modelMapper.args);
            if(index==1) {
                sql = modelMapper.sql;
            }
        }
        long e1 = System.currentTimeMillis();
        if(batchUpdateArgs.size()==0)
            return null;
        long start2 = System.currentTimeMillis();
        JdbcBatchPreparedStatementSetter jdbcBatchPreparedStatementSetter = new JdbcBatchPreparedStatementSetter(batchUpdateArgs);
        int[] result = this.jdbcTemplate.batchUpdate(sql,jdbcBatchPreparedStatementSetter);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]--totalTime:" + (end - start) + " ms={checkModel&modelMapper:" + (e1 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return result;
    }

    @Override
    public <T> int delete(@NotNull T model) {
        if (model == null)
            return 0;
        long start = System.currentTimeMillis();
        this.checkModel(model, SCRIPT_TYPE.DML);
        long e1 = System.currentTimeMillis();
        ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.DELETE);
        long e2 = System.currentTimeMillis();
        long start2 = System.currentTimeMillis();
        int var = this.jdbcTemplate.update(modelMapper.sql, modelMapper.args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + modelMapper.sql + "]--totalTime:" + (end - start) + " ms={checkModel:" + (e1 - start) + " ms,modelMapper:" + (e2 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return var;
    }

    @Override
    public <T> int[] batchDelete(List<T> models) {
        if(models==null||models.size()==0)
            return null;
        long start = System.currentTimeMillis();
        List<Object[]> batchUpdateArgs = new ArrayList<>();
        String sql = "";
        int index = 0;
        for(T model:models) {
            index++;
            this.checkModel(model, SCRIPT_TYPE.DML);
            ModelMapper modelMapper = this.analisisFromModel(model, DML_TYPE.DELETE);
            batchUpdateArgs.add(modelMapper.args);
            if(index==1) {
                sql = modelMapper.sql;
            }
        }
        long e1 = System.currentTimeMillis();
        if(batchUpdateArgs.size()==0)
            return null;
        long start2 = System.currentTimeMillis();
        JdbcBatchPreparedStatementSetter jdbcBatchPreparedStatementSetter = new JdbcBatchPreparedStatementSetter(batchUpdateArgs);
        int[] result = this.jdbcTemplate.batchUpdate(sql,jdbcBatchPreparedStatementSetter);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]--totalTime:" + (end - start) + " ms={checkModel&modelMapper:" + (e1 - start) + " ms,execute sql:" + (end - start2) + " ms}");
        return result;
    }

    @Override
    public int update(String sql, Object... args) {
        long start = System.currentTimeMillis();
        int var = this.jdbcTemplate.update(sql, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return var;
    }

    @Override
    public int update(String sql, List<?> args) {
        Object[] arr = args.toArray();
        return this.update(sql,arr);
    }

    @Override
    public int[] batchUpdate(String sql,List<Object[]> args) {
        long start = System.currentTimeMillis();
        int[] var = this.jdbcTemplate.batchUpdate(sql, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return var;
    }

    @Override
    public int insert(String sql, Object... args) {
        long start = System.currentTimeMillis();
        int var = this.jdbcTemplate.update(sql, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return var;
    }

    @Override
    public int insert(String sql, List<?> args) {
        Object[] arr = args.toArray();
        return this.insert(sql,arr);
    }

    @Override
    public int delete(String sql, Object... args) {
        long start = System.currentTimeMillis();
        int var = this.jdbcTemplate.update(sql, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return var;
    }

    @Override
    public int delete(String sql, List<?> args) {
        Object[] arr = args.toArray();
        return this.delete(sql,arr);
    }

    /**
     * jdbcTemplate 开放sql执行接口，可自定义执行sql
     *
     * @param sql
     * @param action
     * @param <T>
     * @return
     */
    @Override
    public <T> T executeSql(String sql, PreparedStatementCallback<T> action) {
        long start = System.currentTimeMillis();
        T obj = this.jdbcTemplate.execute(sql, action);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return obj;
    }

    /**
     * jdbcTemplate 开放procedure执行接口，可自定义执行call
     *
     * @param callString
     * @param action
     * @param <T>
     * @return
     */
    @Override
    public <T> T executeSql(String callString, CallableStatementCallback<T> action) {
        long start = System.currentTimeMillis();
        T obj = this.jdbcTemplate.execute(callString, action);
        long end = System.currentTimeMillis();
        logger.info("procedure sql:[" + callString + "]  " + (end - start) + " ms");
        return obj;
    }


    @Override
    public <T> T queryObject(@NotNull String sql, @NotNull Class<T> clazz, Object... args) {
        RowMapper rowMapper = null;
        //基本数据类型使用
        if (JAVA_BASIC_TYPE.contains(clazz)) {
            rowMapper = new SingleColumnRowMapper<T>(clazz);
        } else { //pojo使用
            rowMapper = new BeanPropertyRowMapper<T>(clazz);
        }
        long start = System.currentTimeMillis();
        List<T> list = jdbcTemplate.query(sql, rowMapper, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        if (list != null && list.size() > 1)
            throw new JdbcTempleteException("sql[" + sql + "] obtain too many result,except 1 row,actual " + list.size() + " rows");
        return list == null || list.size() == 0 ? null : list.get(0);
    }
    @Override
    public <T> T queryObject(@NotNull String sql, @NotNull Class<T> clazz, List<?> args) {
        Object[] arr = args.toArray();
        return this.queryObject(sql,clazz,arr);
    }

    @Override
    public <T> List<T> queryList(@NotNull String sql, @NotNull Class<T> clazz, Object... args) {
        RowMapper rowMapper = null;
        //基本数据类型使用
        if (JAVA_BASIC_TYPE.contains(clazz)) {
            rowMapper = new SingleColumnRowMapper<T>(clazz);
        } else { //pojo使用
            rowMapper = new BeanPropertyRowMapper<T>(clazz);
        }
        long start = System.currentTimeMillis();
        List<T> list = jdbcTemplate.query(sql, rowMapper, args);
        long end = System.currentTimeMillis();
        logger.info("sql:[" + sql + "]  " + (end - start) + " ms");
        return list;
    }
    @Override
    public <T> List<T> queryList(@NotNull String sql, @NotNull Class<T> clazz, List<?> args) {
        Object[] arr = args.toArray();
        return this.queryList(sql,clazz,arr);
    }

    @Override
    public <T> PageResult<T> queryForPage(String sql, Class<T> clazz,int page,int limit, Object... args) {
        String sql_details = "select * from ("+sql+") _table limit ?,?";
        String sql_count = "select count(1) from ("+sql+") _counttable";
        Object[] args_new = new Object[(args==null?0:args.length)+2];
        for(int i=0;i<args.length;i++) {
            args_new[i] = args[i];
        }
        args_new[args_new.length-2] = page*limit;
        args_new[args_new.length-1] = limit;

        List<T> resultlist = this.queryList(sql_details,clazz,args_new);
        int totalsize = this.queryObject(sql_count,Integer.class,args);
        PageResult<T> pageResult = new PageResult<>();
        pageResult.setCount(totalsize);
        pageResult.setData(resultlist);
        pageResult.setMsg("query success");
        pageResult.setTotalpages(totalsize/limit+(totalsize%limit==0?0:1));
        return pageResult;
    }

    @Override
    public <T> PageResult<T> queryForPage(String sql, Class<T> clazz,int page,int limit, List<?> args) {
        Object[] arr = args.toArray();
        return this.queryForPage(sql,clazz,page,limit,arr);
    }



    @Override
    public <T> T call(@NotNull T procedureModel) {
        if (procedureModel == null)
            return null;
        long start = System.currentTimeMillis();
        this.checkModel(procedureModel, SCRIPT_TYPE.PROCEDURE);
        long e1 = System.currentTimeMillis();
        final ModelMapperProc modelMapperProc = this.analisisProcFromModel(procedureModel);
        long e2 = System.currentTimeMillis();
        long start2 = System.currentTimeMillis();

        this.jdbcTemplate.execute(modelMapperProc.callsql, new CallableStatementCallback<T>() {
            @Override
            public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                Iterator<Integer> iterator = modelMapperProc.callParameterMap.keySet().iterator();
                List<Integer> outindex = new LinkedList<>();
                while (iterator.hasNext()) {
                    int index = iterator.next();
                    CallParameter callParameter = modelMapperProc.callParameterMap.get(index);
                    if (callParameter.isIsout()) {
                        outindex.add(index);
                        switch (callParameter.getColumnType()) {
                            case SHORT:
                                cs.registerOutParameter(index, Types.SMALLINT);
                                break;
                            case INT:
                                cs.registerOutParameter(index, Types.INTEGER);
                                break;
                            case BIGINT:
                                cs.registerOutParameter(index, Types.BIGINT);
                                break;
                            case FLOAT:
                                cs.registerOutParameter(index, Types.FLOAT);
                                break;
                            case DOUBLE:
                                cs.registerOutParameter(index, Types.DOUBLE);
                                break;
                            case BOOLEAN:
                                cs.registerOutParameter(index, Types.BOOLEAN);
                                break;
                            case CHAR:
                                cs.registerOutParameter(index, Types.VARCHAR);
                                break;
                            case STRING:
                                cs.registerOutParameter(index, Types.VARCHAR);
                                break;
                            case DATE:
                                cs.registerOutParameter(index, Types.DATE);
                                break;
                            case TIMESTAMP:
                                cs.registerOutParameter(index, Types.TIMESTAMP);
                                break;
                            case BYTES:
                                throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is blob or bytes");
                            case TEXT:
                                throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is clob");
                        }
                    } else {
                        switch (callParameter.getColumnType()) {
                            case SHORT:
                                if (callParameter.getVal() == null)
                                    cs.setShort(index, (short) 0);
                                else
                                    cs.setShort(index, (short) callParameter.getVal());
                                break;
                            case INT:
                                if (callParameter.getVal() == null)
                                    cs.setInt(index, 0);
                                else
                                    cs.setInt(index, (int) callParameter.getVal());
                                break;
                            case BIGINT:
                                if (callParameter.getVal() == null)
                                    cs.setLong(index, 0);
                                else
                                    cs.setLong(index, (long) callParameter.getVal());
                                break;
                            case FLOAT:
                                if (callParameter.getVal() == null)
                                    cs.setFloat(index, 0);
                                else
                                    cs.setFloat(index, (float) callParameter.getVal());
                                break;
                            case DOUBLE:
                                if (callParameter.getVal() == null)
                                    cs.setDouble(index, 0);
                                else
                                    cs.setDouble(index, (double) callParameter.getVal());
                                break;
                            case BOOLEAN:
                                if (callParameter.getVal() == null)
                                    cs.setBoolean(index, false);
                                else
                                    cs.setBoolean(index, (boolean) callParameter.getVal());
                                break;
                            case CHAR:
                                if (callParameter.getVal() == null)
                                    cs.setString(index, null);
                                else
                                    cs.setString(index, callParameter.getVal().toString());
                                break;
                            case STRING:
                                if (callParameter.getVal() == null)
                                    cs.setString(index, null);
                                else
                                    cs.setString(index, callParameter.getVal().toString());
                                break;
                            case DATE:
                                if (callParameter.getVal() == null)
                                    cs.setDate(index, null);
                                else {
                                    Date date = (Date) callParameter.getVal();
                                    java.sql.Date sqlDate = new java.sql.Date(date.getTime());
                                    cs.setDate(index, sqlDate);
                                }
                                break;
                            case TIMESTAMP:
                                if (callParameter.getVal() == null)
                                    cs.setTimestamp(index, null);
                                else
                                    cs.setTimestamp(index, (Timestamp) callParameter.getVal());
                                break;
                            case BYTES:
                                throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is blob or bytes");
                            case TEXT:
                                throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is clob");
                        }
                    }
                }

                //参数设置完毕，执行存过
                cs.execute();

                //存过执行结束，开始回写model的out参数
                if (outindex.size() > 0) {
                    for (int index : outindex) {
                        CallParameter callParameter = modelMapperProc.callParameterMap.get(index);
                        try {
                            switch (callParameter.getColumnType()) {
                                case SHORT:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), short.class)
                                            .invoke(procedureModel, cs.getShort(index));
                                    break;
                                case INT:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), int.class)
                                            .invoke(procedureModel, cs.getInt(index));
                                    break;
                                case BIGINT:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), long.class)
                                            .invoke(procedureModel, cs.getLong(index));
                                    break;
                                case FLOAT:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), float.class)
                                            .invoke(procedureModel, cs.getFloat(index));
                                    break;
                                case DOUBLE:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), double.class)
                                            .invoke(procedureModel, cs.getDouble(index));
                                    break;
                                case BOOLEAN:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), boolean.class)
                                            .invoke(procedureModel, cs.getBoolean(index));
                                    break;
                                case CHAR:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), String.class)
                                            .invoke(procedureModel, cs.getString(index));
                                    break;
                                case STRING:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), String.class)
                                            .invoke(procedureModel, cs.getString(index));
                                    break;
                                case DATE:
                                    Date date = new Date(cs.getDate(index).getTime());
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), Date.class)
                                            .invoke(procedureModel, date);
                                    break;
                                case TIMESTAMP:
                                    procedureModel.getClass().getMethod("set" + upperFirstChar(callParameter.getFieldname()), Timestamp.class)
                                            .invoke(procedureModel, cs.getTimestamp(index));
                                    break;
                                case BYTES:
                                    throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is blob or bytes");
                                case TEXT:
                                    throw new JdbcTempleteException("[" + procedureModel.getClass().getName() + "." + callParameter.getFieldname() + "]'s type is clob");
                            }
                        } catch (IllegalAccessException e) {
                            throw new JdbcTempleteException(e.getMessage());
                        } catch (InvocationTargetException e) {
                            throw new JdbcTempleteException(e.getMessage());
                        } catch (NoSuchMethodException e) {
                            throw new JdbcTempleteException(e.getMessage());
                        }

                    }
                }
                return null;
            }
        });

        long end = System.currentTimeMillis();
        logger.info("sql:[" + modelMapperProc.callsql + "]--totalTime:" + (end - start) + " ms={checkModel:" + (e1 - start) + " ms,modelMapper:" + (e2 - start) + " ms,execute procedure:" + (end - start2) + " ms}");
        return procedureModel;
    }


    private void checkModel(Object model, SCRIPT_TYPE scriptType) {
        if (model == null)
            throw new JdbcTempleteException("model不能为空");

        if (JAVA_BASIC_TYPE.contains(model.getClass()))
            throw new JdbcTempleteException("model不能为JAVA的基本数据类型");

        if (scriptType == SCRIPT_TYPE.DML) {
            if (!CheckAnnotationUtils.isAnnotationPersistent(model.getClass(), Table.class))
                throw new JdbcTempleteException("model中缺少@Table注解");
        } else {
            if (!CheckAnnotationUtils.isAnnotationPersistent(model.getClass(), Procedure.class))
                throw new JdbcTempleteException("model中缺少@Procedure注解");
        }
    }


    private ModelMapper analisisFromModel(Object model, DML_TYPE dml_type) {
        StringBuffer sb = new StringBuffer();
        Class clazz = model.getClass();
        //获取dbname.tablename
        String tableName = "";
        if (AnnotationUtils.findAnnotation(clazz, Table.class).dbName() == null
                || AnnotationUtils.findAnnotation(clazz, Table.class).dbName().equals("")) {
            tableName = AnnotationUtils.findAnnotation(clazz, Table.class).value();
        } else {
            tableName = AnnotationUtils.findAnnotation(clazz, Table.class).dbName() + "."
                    + AnnotationUtils.findAnnotation(clazz, Table.class).value();
        }

        StringBuffer $end = new StringBuffer();

        switch (dml_type) {
            case INSERT:
                sb.append(" INSERT INTO ")
                        .append(tableName)
                        .append("(");
                $end.append(" VALUES( ");
                break;
            case UPDATE:
                sb.append(" UPDATE ")
                        .append(tableName)
                        .append(" SET ");
                break;
            case DELETE:
                sb.append(" DELETE FROM ")
                        .append(tableName);
                break;
            default:
                throw new JdbcTempleteException("TYPE:[" + dml_type + "] CAN NOT FIND FROM [" + this.getClass().getName() + ".DML_TYPE]");
        }


        //1、获取primarykey对象
        //2、获取@column的fields数组
        List<Object> $args = new ArrayList<>();
        boolean containsLob = false;
        Object primaryKeyVal = null;
        ModelMapper modelMapper = new ModelMapper();
        for (Field field : clazz.getDeclaredFields()) {
            PrimaryKey var1 = AnnotationUtils.findAnnotation(field, PrimaryKey.class);
            Column var2 = AnnotationUtils.findAnnotation(field, Column.class);
            //delete操作，获取column没有意义，跳过
            if (var2 != null && dml_type != DML_TYPE.DELETE) {
                Object fieldVal = null;
                try {
                    fieldVal = clazz.getMethod("get" + upperFirstChar(field.getName())).invoke(model);
                    validType(fieldVal, var2.columnType(), field, var2.columnSize(), var2.IsNull(),false);
                    if (var2.columnType() == ColumnType.TEXT || var2.columnType() == ColumnType.BYTES)
                        containsLob = true;
                } catch (IllegalAccessException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (InvocationTargetException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (NoSuchMethodException e) {
                    throw new JdbcTempleteException(e.getMessage());
                }

                if (dml_type == DML_TYPE.INSERT) {
                    sb.append(var2.column()).append(",");
                    $end.append("?,");
                    $args.add(fieldVal);
                } else if (dml_type == DML_TYPE.UPDATE) {
                    sb.append(var2.column()).append("=?,");
                    $args.add(fieldVal);
                }
                //@Column和@PrimaryKey不可能同时存在
                continue;
            }

            if (var1 != null) {
                Object fieldVal = null;
                modelMapper.primarykeynames = new String[]{var1.columnName()};
                modelMapper.primaryfilednames = new String[]{field.getName()};
                modelMapper.primaryfiledtype = field.getType();
                try {
                    fieldVal = clazz.getMethod("get" + upperFirstChar(field.getName())).invoke(model);
                } catch (IllegalAccessException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (InvocationTargetException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (NoSuchMethodException e) {
                    throw new JdbcTempleteException(e.getMessage());
                }
                //delete操作,主键获取的就没有循环的必要了
                if (dml_type == DML_TYPE.DELETE) {
                    sb.append(" where ").append(var1.columnName()).append("=?");
                    $args.add(fieldVal);
                    break;
                } else if (dml_type == DML_TYPE.INSERT) {
                    //主键的时候，只有insert操作需要校验数据长度
                    if (var1.inrementType() != IncrementType.AUTOINCREMENT) {
                        validType(fieldVal, var1.columnType(), field, var1.columnSize(), var1.IsNull(),true);
                        sb.append(var1.columnName()).append(",");
                    }


                    //获取主键的值，如果是sequence就自动获取，否则获取fieldVal
                    if (var1.inrementType() == IncrementType.SEQUENCE) {
                        validType(fieldVal, var1.columnType(), field, var1.columnSize(), var1.IsNull(),true);
                        Sequence sequence = AnnotationUtils.findAnnotation(field, Sequence.class);
                        String sequenceName = sequence.dbName().equals("") ? sequence.value() : sequence.dbName() + "." + sequence.value();
                        $end.append(sequenceName).append(".nextval,");
                    } else if (var1.inrementType() != IncrementType.AUTOINCREMENT) {
                        validType(fieldVal, var1.columnType(), field, var1.columnSize(), var1.IsNull(),false);
                        $end.append("?,");
                        $args.add(fieldVal);
                    }
                } else if (dml_type == DML_TYPE.UPDATE) {
                    $end.append(" where ").append(var1.columnName()).append("=?");
                    primaryKeyVal = fieldVal;
                }
            }
            //因为在项目启动时已经扫描过主键不能为空的情况
        }

        String sql = sb.toString();
        String endstr = $end.toString();
        if (dml_type == DML_TYPE.INSERT) {
            sql = sql.substring(0, sql.length() - 1) + ")" + endstr.substring(0, endstr.length() - 1) + ")";
        } else if (dml_type == DML_TYPE.UPDATE) {
            sql = sql.substring(0, sql.length() - 1) + endstr;
            $args.add(primaryKeyVal);
        }


        modelMapper.containsLob = containsLob;
        modelMapper.sql = sql;
        modelMapper.args = $args.toArray();

        return modelMapper;
    }


    private ModelMapperProc analisisProcFromModel(Object model) {
        StringBuffer sb = new StringBuffer();
        Class clazz = model.getClass();
        //获取dbname.procedureName
        String procedureName = "";
        if (AnnotationUtils.findAnnotation(clazz, Procedure.class).dbName() == null
                || AnnotationUtils.findAnnotation(clazz, Procedure.class).dbName().equals("")) {
            procedureName = AnnotationUtils.findAnnotation(clazz, Procedure.class).value();
        } else {
            procedureName = AnnotationUtils.findAnnotation(clazz, Procedure.class).dbName() + "."
                    + AnnotationUtils.findAnnotation(clazz, Procedure.class).value();
        }

        sb.append("{CALL ").append(procedureName).append("(");
        Map<Integer, CallParameter> callParameterMap = new HashMap<>();
        boolean containsLob = false;
        //分析注解生成数据
        for (Field field : clazz.getDeclaredFields()) {
            ProcedureType procedureType = AnnotationUtils.findAnnotation(field, ProcedureType.class);
            if (procedureType != null) {
                if (procedureType.columnType() == ColumnType.TEXT || procedureType.columnType() == ColumnType.BYTES)
                    containsLob = true;
                CallParameter callParameter = new CallParameter();
                callParameter.setColumnType(procedureType.columnType());
                Object fieldVal = null;
                try {
                    fieldVal = clazz.getMethod("get" + upperFirstChar(field.getName())).invoke(model);
                } catch (IllegalAccessException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (InvocationTargetException e) {
                    throw new JdbcTempleteException(e.getMessage());
                } catch (NoSuchMethodException e) {
                    throw new JdbcTempleteException(e.getMessage());
                }
                callParameter.setFieldname(field.getName());
                callParameter.setVal(fieldVal);
                callParameter.setIsout(procedureType.paramType() == ParamType.PARAMOUT ? true : false);
                callParameterMap.put(procedureType.paramIndex(), callParameter);
                sb.append("?,");
            }
        }
        String callsql = sb.toString();
        if (!callParameterMap.isEmpty())
            callsql = callsql.substring(0, callsql.length() - 1);
        callsql += ")}";
        ModelMapperProc modelMapperProc = new ModelMapperProc();
        modelMapperProc.callsql = callsql;
        modelMapperProc.callParameterMap = callParameterMap;
        modelMapperProc.containsLob = containsLob;

        return modelMapperProc;
    }

    //DML model映射
    private class ModelMapper {
        public String sql;
        public Object[] args;
        public String[] primarykeynames;
        public String[] primaryfilednames;
        public Class<?> primaryfiledtype;
        public boolean containsLob = false; //是否包含clob、blob字段
    }

    //procedure等脚本 model映射
    private class ModelMapperProc {
        public String callsql;
        public Map<Integer, CallParameter> callParameterMap;
        public boolean containsLob = false; //是否包含clob、blob字段
    }


    private String upperFirstChar(String fieldName) {
        char[] chars = fieldName.toCharArray();
        if (chars[0] >= 'a' && chars[0] <= 'z')
            chars[0] -= 32;
        return String.valueOf(chars);
    }

    /**
     * 数据合法性校验
     * @param fieldVal 属性值
     * @param columnType column的类型
     * @param field 属性
     * @param columnSize column的最大长度
     * @param isNull 是否可以为null
     * @param ignoreIsNull 是否跳过 null合法性检测
     */
    private void validType(Object fieldVal, ColumnType columnType, Field field, int columnSize, boolean isNull,boolean ignoreIsNull) {
        if (!ignoreIsNull&&!isNull && fieldVal == null)
            throw new JdbcTempleteException(field.getName() + "can not be NULL");

        switch (columnType) {
            case SHORT:
                if (columnSize > 5) {
                    throw new JdbcTempleteException(field.getName() + " is SHORT type,columnSize must be less than or equal to 5");
                }
                if(columnSize==-1)
                    break;
                if (fieldVal != null && String.valueOf(fieldVal).getBytes().length > columnSize) {
                    throw new JdbcTempleteException(field.getName() + "'s value is out of range");
                }
                break;
            case INT:
                if (columnSize > 10) {
                    throw new JdbcTempleteException(field.getName() + " is INT type,columnSize must be less than or equal to 10");
                }
                if(columnSize==-1)
                    break;
                if (fieldVal != null && String.valueOf(fieldVal).getBytes().length > columnSize) {
                    throw new JdbcTempleteException(field.getName() + "'s value is out of range");
                }
                break;
            case BIGINT:
                if (columnSize > 19) {
                    throw new JdbcTempleteException(field.getName() + " is LONG type,columnSize must be less than or equal to 19");
                }
                if(columnSize==-1)
                    break;
                if (fieldVal != null && String.valueOf(fieldVal).getBytes().length > columnSize) {
                    throw new JdbcTempleteException(field.getName() + "'s value is out of range");
                }
                break;
            case BYTES:
                break;
            case FLOAT:
                break;
            case DOUBLE:
                break;
            case BOOLEAN:
                break;
            case CHAR:
                if(columnSize==-1)
                    break;
                if (fieldVal != null && String.valueOf(fieldVal).getBytes().length > columnSize) {
                    throw new JdbcTempleteException(field.getName() + "'s value is out of range");
                }
                break;
            case STRING:
                if(columnSize==-1)
                    break;
                if (fieldVal != null && String.valueOf(fieldVal).getBytes().length > columnSize) {
                    throw new JdbcTempleteException(field.getName() + "'s value is out of range");
                }
                break;
            case TEXT:
                break;
            case DATE:
                break;
            case TIMESTAMP:
                break;
            default:
                throw new JdbcTempleteException(field.getName() + "'s type [" + columnType + "] is unknown");
        }
    }
}
